 /**
   * Copyright 2014 Viettel Telecom. All rights reserved.
   * VIETTEL PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
   */
 package com.viettel.core.dao.cms; 
import java.util.ArrayList; 
import java.util.List; 

import org.hibernate.type.StandardBasicTypes; 
import org.hibernate.type.Type; 
import org.springframework.beans.factory.annotation.Autowired; 

import com.viettel.core.common.utils.StringUtil;
import com.viettel.core.exceptions.DataAccessException; 
import com.viettel.core.entities.cms.GroupCategory;
import com.viettel.core.entities.cms.TechniqueCategory;
import com.viettel.core.entities.vo.TechniqueCategorySearchFilter;
import com.viettel.core.entities.vo.TechniqueCategorySearchVO;
import com.viettel.core.enumtype.GroupCategoryStatus;
import com.viettel.core.enumtype.TechniqueCategoryStatus;
import com.viettel.core.repo.IRepository;
 /**
   *  Mo ta muc dich cua lop (interface)
   *  @author: TOANTM1
   *  @version:  
   *  @since: 27/02/2014 - 11:34:02
   */
 public class TechniqueCategoryDAOImpl implements TechniqueCategoryDAO { 
	 private IRepository repo; 
		
	 	public void setRepo(IRepository repo) {
			this.repo = repo;
		}
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public TechniqueCategory getTechniqueCategoryById(Integer id) throws DataAccessException { 
		return repo.getEntityById(TechniqueCategory.class, id); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public TechniqueCategory createTechniqueCategory(TechniqueCategory techniquecategory) 
			throws DataAccessException { 
		if (techniquecategory == null) { 
			throw new IllegalArgumentException("techniquecategory"); 
		} 
		repo.create(techniquecategory); 
		return repo.getEntityById(TechniqueCategory.class, techniquecategory.getId()); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public void updateTechniqueCategory(TechniqueCategory techniquecategory) throws DataAccessException { 
		if (techniquecategory == null) { 
			throw new IllegalArgumentException("techniquecategory"); 
		} 
		repo.update(techniquecategory); 
	} 
 
 	/**@author TOANTM1
  	 * 27/02/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
 	@Override 
	public void deleteTechniqueCategory(TechniqueCategory techniquecategory) throws DataAccessException { 
		if (techniquecategory == null) { 
			throw new IllegalArgumentException("techniquecategory"); 
		} 
		repo.delete(techniquecategory); 
	} 
 	
 	/**
 	 * @author tuantm20
 	 * @throws DataAccessException
 	 */
 	@Override
	public List<TechniqueCategory> getListTechniqueCategoryByParentId(Integer parentId)
			throws DataAccessException {
		List<Object> params = new ArrayList<Object>();
		String sql = "SELECT * FROM technique_category  where status != ? AND parent_id = ? ORDER BY LOWER(name)";
		//String sql = "SELECT * FROM technique_category  START WITH id = ? CONNECT BY PRIOR id = parent_id ORDER BY id";
		params.add(TechniqueCategoryStatus.DELETED.getValue());
		params.add(parentId);
		return repo.getListBySQL(TechniqueCategory.class, sql, params);
	}

 	/**
 	 * @author tuantm20
 	 * @throws DataAccessException
 	 */
	@Override
	public List<TechniqueCategory> getListParentTechniqueCategory(Integer parentId)
			throws DataAccessException {
		List<Object> params = new ArrayList<Object>();
		String sql = "select * from technique_category where status != ? AND parent_id is null ";
		params.add(TechniqueCategoryStatus.DELETED.getValue());
		if (parentId != null) {
			sql += " and id != ? ";
			params.add(parentId);
		}
		sql += " ORDER BY LOWER(name) ";
		return repo.getListBySQL(TechniqueCategory.class, sql, params);
	} 
	
	/**
 	 * @author tuantm20
 	 * @throws DataAccessException
 	 */
	@Override
	public List<TechniqueCategory> getAllTechniqueCategory()
			throws DataAccessException {
		List<Object> params = new ArrayList<Object>();
		String sql = "select * from technique_category where status != ? ORDER BY LOWER(name)";
		params.add(TechniqueCategoryStatus.DELETED.getValue());
		return repo.getListBySQL(TechniqueCategory.class, sql, params);
	} 
	
	/**
 	 * @author tuantm20
 	 * @throws DataAccessException
 	 */
	@Override
	public List<TechniqueCategorySearchVO> getListTechniqueCategoryByFilter(TechniqueCategorySearchFilter filter)
			throws DataAccessException {
		List<Object> paramSelect = new ArrayList<Object>();
		List<Object> countParams = new ArrayList<Object>();
		StringBuilder selectSql = new StringBuilder();
		StringBuilder countQuery = new StringBuilder();
		StringBuilder fromQuery = new StringBuilder();
		
		selectSql.append(" SELECT tc.id  	as id, ");
		selectSql.append(" tc.name 			as name, ");
		selectSql.append(" tc.description 	as description, ");
		selectSql.append(" tc.status 		as status, ");
		selectSql.append(" tc.parent_id 	as parentId, ");
		selectSql.append(" tcp.name 		as parentName ");
		
		fromQuery.append(" FROM technique_category tc ");
		fromQuery.append(" 		LEFT JOIN technique_category tcp ");
		fromQuery.append(" 			ON tc.parent_id = tcp.id ");
		
		fromQuery.append(" WHERE tc.status != ? ");
		paramSelect.add(TechniqueCategoryStatus.DELETED.getValue());
		countParams.add(TechniqueCategoryStatus.DELETED.getValue());
		
		if (filter.getId() != null) {
			fromQuery.append(" and tc.id = ? ");
			paramSelect.add(filter.getId());
			countParams.add(filter.getId());
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getNameText())) {
			fromQuery.append(" and (upper(tc.name_text) like upper(?) ESCAPE '/' )");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getNameText())+ "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getNameText())+ "%");
		}
		
		if (!StringUtil.isNullOrEmpty(filter.getDescription())) {
			fromQuery.append(" and (upper(tc.description) like upper(?) ESCAPE '/' )");
			paramSelect.add("%" + StringUtil.toSearchText(filter.getDescription())+ "%");
			countParams.add("%" + StringUtil.toSearchText(filter.getDescription())+ "%");
		}
		
		if (filter.getStatus() != null) {
			fromQuery.append(" and tc.status = ? ");
			paramSelect.add(filter.getStatus());
			countParams.add(filter.getStatus());
		}
		
		if (filter.getParentId() != null) {
			fromQuery.append(" and tc.parent_id = ? ");
			paramSelect.add(filter.getParentId());
			countParams.add(filter.getParentId());
		}
		selectSql.append(fromQuery).append(" ORDER BY tc.CREATED_TIME DESC , tc.id DESC");
		
		countQuery.append("SELECT COUNT(*) AS COUNT ").append(fromQuery);

		String[] fieldNames = new String[] { 
				"id", "name", "description", 
				"status", "parentId", "parentName"
				};
		
		Type[] fieldTypes = new Type[] { 
				StandardBasicTypes.INTEGER,StandardBasicTypes.STRING, StandardBasicTypes.STRING,
				StandardBasicTypes.INTEGER, StandardBasicTypes.INTEGER,	StandardBasicTypes.STRING
				};

		List<Class<?>> synchronizedClass = new ArrayList<Class<?>>();
		synchronizedClass.add(TechniqueCategory.class);

		List<TechniqueCategorySearchVO> list = null;
		if (filter.getPaging() != null) {
			list = repo.getListByQueryAndScalarPaginated(
					TechniqueCategorySearchVO.class, fieldNames, fieldTypes,
					selectSql.toString(), countQuery.toString(), paramSelect,
					countParams, filter.getPaging(), synchronizedClass);
		} else {
			list = repo.getListByQueryAndScalar(TechniqueCategorySearchVO.class,
					fieldNames, fieldTypes, selectSql.toString(), paramSelect,
					synchronizedClass);
		}

		return list;		
	}
	/**@author HUYNHDC
  	 * 24/05/2014 - 11:34:02
  	 * For: 
  	 * @description: 
  	 */
	@Override
	public List<TechniqueCategory> getListTechniqueCategoryParent()
			throws DataAccessException {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * ");
		sql.append("FROM technique_category ");
		sql.append("WHERE parent_id IS NULL ");
		sql.append("AND status != -1 ");
		sql.append("ORDER BY name ");
		return repo.getListBySQL(TechniqueCategory.class, sql.toString(), null);
	}
	
	@Override
	public List<TechniqueCategory> getListTechniqueCategoryNotParent()
			throws DataAccessException {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT * ");
		sql.append("FROM technique_category ");
		sql.append("WHERE parent_id IS NOT NULL ");
		sql.append("AND status != -1 ");
		sql.append("ORDER BY name ");
		return repo.getListBySQL(TechniqueCategory.class, sql.toString(), null);
	}
	@Override
	public Boolean checkNameExist(String userName) throws DataAccessException {
		StringBuilder sql = new StringBuilder();
		List<Object> params = new ArrayList<Object>();
		sql.append(" select count(1) count from dual where exists (select 1 from TECHNIQUE_CATEGORY where Upper(NAME_TEXT) = ? and status != ?)");
		params.add(userName.toUpperCase());
		params.add(GroupCategoryStatus.DELETED.getValue());
		int c = repo.countBySQL(sql.toString(), params);
		return c > 0 ? true : false;
	} 
} 
 
